package com.marvels.financial.collection.service.impl;

import java.math.BigDecimal;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.marvels.common.exception.MarvelsBusinessException;
import com.marvels.common.utils.DateUtils;
import com.marvels.financial.collection.dao.OverdueCollectMapper;
import com.marvels.financial.collection.dto.CollectListConditionDTO;
import com.marvels.financial.collection.dto.OutSideDownDTO;
import com.marvels.financial.collection.service.CollectManageService;

@Service
public class CollectManageServiceImpl implements CollectManageService{
	@Autowired
	private OverdueCollectMapper overdueCollectMapper;
	@Override
	public HSSFWorkbook downOutsideExcel(CollectListConditionDTO condition) throws MarvelsBusinessException {
		List<OutSideDownDTO> outsides = overdueCollectMapper.downOutsideExcel(condition);
		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet("firstSheet");
		HSSFRow row = sheet.createRow((int) 0);
		sheet.setDefaultColumnWidth(10);
		// 创建单元格，并设置值表头 设置表头居中
		HSSFCellStyle style = workbook.createCellStyle();
		// 居中格式、加粗
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		org.apache.poi.hssf.usermodel.HSSFFont font = workbook.createFont();
		font.setFontHeightInPoints((short) 11);
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		style.setBorderBottom((short) 1);// 下边框
		style.setBorderLeft((short) 1);// 左边框
		style.setBorderRight((short) 1);// 右边框
		style.setBorderTop((short) 1);// 上边框
		style.setLocked(true);
		style.setFont(font);
		// 设置表内容格式
		HSSFCellStyle style2 = workbook.createCellStyle();
		style2.setBorderBottom((short) 1);// 下边框
		style2.setBorderLeft((short) 1);// 左边框
		style2.setBorderRight((short) 1);// 右边框
		style2.setBorderTop((short) 1);// 上边框
		style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		HSSFCell cell = row.createCell(0);
		cell.setCellValue("序号");
		cell.setCellStyle(style);
		
		cell = row.createCell((short) 1);
		cell.setCellValue("渠道");
		cell.setCellStyle(style);
		
		cell = row.createCell((short) 2);
		cell.setCellValue("地区");
		cell.setCellStyle(style);
		
		cell = row.createCell((short) 3);
		cell.setCellValue("产品名称");
		cell.setCellStyle(style);
		
		cell = row.createCell((short) 4);
		cell.setCellValue("申请编号");
		cell.setCellStyle(style);
		
		cell = row.createCell((short) 5);
		cell.setCellValue("催收渠道");
		cell.setCellStyle(style);
		
		cell = row.createCell((short) 6);
		cell.setCellValue("债务人姓名");
		cell.setCellStyle(style);
		cell = row.createCell((short) 7);
		cell.setCellValue("婚姻状况");
		cell.setCellStyle(style);
		
		cell = row.createCell((short) 8);
		cell.setCellValue("性别");
		cell.setCellStyle(style);
		cell = row.createCell((short) 9);
		cell.setCellValue("债务人身份证号码");
		cell.setCellStyle(style);
		cell = row.createCell((short) 10);
		cell.setCellValue("债务人电话号码");
		cell.setCellStyle(style);
		cell = row.createCell((short) 11);
		cell.setCellValue("委案金额/逾期金额");
		cell.setCellStyle(style);
		cell = row.createCell((short) 12);
		cell.setCellValue("逾期本金");
		cell.setCellStyle(style);
		cell = row.createCell((short) 13);
		cell.setCellValue("逾期利息");
		cell.setCellStyle(style);
		cell = row.createCell((short) 14);
		cell.setCellValue("逾期管理费");
		cell.setCellStyle(style);
		
		cell = row.createCell((short) 15);
		cell.setCellValue("违约金（滞纳金）");
		cell.setCellStyle(style);
		
		//*********************
		cell = row.createCell((short) 16);
		cell.setCellValue("已还期数");
		cell.setCellStyle(style);
		
		cell = row.createCell((short) 17);
		cell.setCellValue("末次还款时间");
		cell.setCellStyle(style);
		cell = row.createCell((short) 18);
		cell.setCellValue("末次还款金额");
		cell.setCellStyle(style);
		cell = row.createCell((short) 19);
		cell.setCellValue("未还本金/未结清金额");
		cell.setCellStyle(style);
		cell = row.createCell((short) 20);
		cell.setCellValue("贷款日期");
		cell.setCellStyle(style);
		cell = row.createCell((short) 21);
		cell.setCellValue("贷款金额");
		cell.setCellStyle(style);
		
		cell = row.createCell((short) 22);
		cell.setCellValue("每月正常应还款");
		cell.setCellStyle(style);
		cell = row.createCell((short) 23);
		cell.setCellValue("逾期初始日期");
		cell.setCellStyle(style);
		
		//*********
		cell = row.createCell((short) 24);
		cell.setCellValue("逾期天数");
		cell.setCellStyle(style);
		cell = row.createCell((short) 25);
		cell.setCellValue("逾期类型");
		cell.setCellStyle(style);
		cell = row.createCell((short) 26);
		cell.setCellValue("还款帐号");
		cell.setCellStyle(style);
		//*****************
		cell = row.createCell((short) 27);
		cell.setCellValue("账户名");
		cell.setCellStyle(style);
		cell = row.createCell((short) 28);
		cell.setCellValue("开户行");
		cell.setCellStyle(style);
		cell = row.createCell((short) 29);
		cell.setCellValue("户籍地址");
		cell.setCellStyle(style);
		cell = row.createCell((short) 30);
		cell.setCellValue("居住地址");
		cell.setCellStyle(style);
		cell = row.createCell((short) 31);
		cell.setCellValue("单位名称");
		cell.setCellStyle(style);
		cell = row.createCell((short) 32);
		cell.setCellValue("单位地址");
		cell.setCellStyle(style);
		//************
		cell = row.createCell((short) 33);
		cell.setCellValue("单位电话");
		cell.setCellStyle(style);
		cell = row.createCell((short) 34);
		cell.setCellValue("联系人1");
		cell.setCellStyle(style);
		cell = row.createCell((short) 35);
		cell.setCellValue("关系 ");
		cell.setCellStyle(style);
		cell = row.createCell((short) 36);
		cell.setCellValue("电话");
		cell.setCellStyle(style);
		cell = row.createCell((short) 37);
		cell.setCellValue("联系人2");
		cell.setCellStyle(style);
		cell = row.createCell((short) 38);
		cell.setCellValue("关系 ");
		cell.setCellStyle(style);
		cell = row.createCell((short) 39);
		cell.setCellValue("电话");
		cell.setCellStyle(style);
		cell = row.createCell((short) 40);
		cell.setCellValue("委托期限");
		cell.setCellStyle(style);
		cell = row.createCell((short) 41);
		cell.setCellValue("委案日");
		cell.setCellStyle(style);
		cell = row.createCell((short) 42);
		cell.setCellValue("退案日");
		cell.setCellStyle(style); 
		if (null != outsides && outsides.size() > 0) {
			int size = outsides.size();
			for (int i = 0; i < size; i++) {
				OutSideDownDTO dto = outsides.get(i);
				row = sheet.createRow((int) i + 1);
				// 创建单元格，并设置值
				row.createCell(0).setCellValue(String.valueOf(i + 1));//序号
				row.createCell(1).setCellValue(dto.getChannelName());//渠道
				row.createCell(2).setCellValue(dto.getCityName());//地区
				row.createCell(3).setCellValue(dto.getProductTypeName());//产品名称 DateUtils.getDateYYYY_MM_DD(dto.getLoanDate())
				row.createCell(4).setCellValue(dto.getLoanId());//申请编号
				row.createCell(5).setCellValue("");//催收渠道
				row.createCell(6).setCellValue(dto.getCustName());//债务人姓名
				row.createCell(7).setCellValue("");//婚姻状况
				row.createCell(8).setCellValue(dto.getSexName());//性别
				row.createCell(9).setCellValue(dto.getCustCardNo());//债务人身份证号码
				row.createCell(10).setCellValue(dto.getMobile());//债务人电话号码
				row.createCell(11).setCellValue(dto.getOverdueCurrentTotalAmt()==null?"":dto.getOverdueCurrentTotalAmt().toString());//委案金额/逾期金额 
				row.createCell(12).setCellValue(dto.getOverdueCurrentPrincipalAmt()==null?"":dto.getOverdueCurrentPrincipalAmt().toString());//逾期本金
				row.createCell(13).setCellValue(dto.getOverdueCurrentInterestAmt()==null?"":dto.getOverdueCurrentInterestAmt().toString());//逾期利息
				row.createCell(14).setCellValue(dto.getOverdueCurrentManagementAmt()==null?"":dto.getOverdueCurrentManagementAmt().toString());//逾期管理费
				BigDecimal pen = dto.getOverdueCurrentPenaltyAmt()==null?new BigDecimal(0):dto.getOverdueCurrentPenaltyAmt();
				BigDecimal late = dto.getOverdueCurrentLateAmt()==null?new BigDecimal(0):dto.getOverdueCurrentLateAmt();
				row.createCell(15).setCellValue(late.add(pen).toString()); //违约金（滞纳金） 
				row.createCell(16).setCellValue(dto.getAlreadyRepaidTenor()==null?"":dto.getAlreadyRepaidTenor().toString()); //已还期数
				row.createCell(16).setCellValue(dto.getLastRepaymentDate()==null?"":DateUtils.getDateYYYY_MM_DD(dto.getLastRepaymentDate())); //末次还款时间
				row.createCell(16).setCellValue(getNotBig(dto.getLastRepaymentAmt())); //末次还款金额 
				row.createCell(16).setCellValue(dto.getLastRepaymentTenor()==null?"":dto.getLastRepaymentTenor().toString()); //未还期数 
				row.createCell(16).setCellValue(getNotBig(dto.getUnpaidPrincipal())); //未还本金/未结清金额
				row.createCell(16).setCellValue(dto.getStartDate()==null?"":DateUtils.getDateYYYY_MM_DD(dto.getStartDate())); //贷款日期
				row.createCell(16).setCellValue(getNotBig(dto.getLoanAmount())); //贷款金额
				row.createCell(16).setCellValue(getNotBig(dto.getEveryMonthRepayment())); //每月正常应还款
				row.createCell(16).setCellValue(dto.getInputDate()==null?"":DateUtils.getDateYYYY_MM_DD(dto.getInputDate())); //  逾期初始日期 
				row.createCell(16).setCellValue(dto.getOverdueCurrentTotalDay()==null?"":dto.getOverdueCurrentTotalDay().toString()); //逾期天数
				row.createCell(16).setCellValue(getNotNull(dto.getOverdueCurrentType())); //逾期类型
				row.createCell(16).setCellValue(getNotNull(dto.getPaymentAcctNo())); // 还款帐号
				row.createCell(16).setCellValue(getNotNull(dto.getPaymentAcctName())); //账户名
				row.createCell(16).setCellValue(getNotNull(dto.getPaymentBankHead())); //开户行
				row.createCell(16).setCellValue(getNotNull(dto.getPermanentAddress())); // 户籍地址
				row.createCell(16).setCellValue(getNotNull(dto.getResidentialAddress())); //居住地址
				row.createCell(16).setCellValue(getNotNull(dto.getCompanyName())); //单位名称
				row.createCell(16).setCellValue(getNotNull(dto.getCompanyAddress())); //单位地址   
				row.createCell(16).setCellValue(getNotNull(dto.getCompanyPhone())); //单位电话
				row.createCell(16).setCellValue(getNotNull(dto.getContactsName())); //联系人1
				row.createCell(16).setCellValue(getNotNull(dto.getContactsRelation())); //关系 
				row.createCell(16).setCellValue(getNotNull(dto.getContactsPhone())); //电话
				row.createCell(16).setCellValue(getNotNull(dto.getContactsName2())); //联系人2
				row.createCell(16).setCellValue(getNotNull(dto.getContactsRelation2())); //关系 
				row.createCell(16).setCellValue(getNotNull(dto.getContactsPhone2())); //电话
				row.createCell(16).setCellValue(2); //委托期限  2个月	【下载日】	【下载日+2个月】
				row.createCell(16).setCellValue(DateUtils.getDateYYYY_MM_DD(new Date())); //委案日
				Calendar cal = Calendar.getInstance();
				cal.add(Calendar.MONTH,2);
				row.createCell(16).setCellValue(DateUtils.getDateYYYY_MM_DD(cal.getTime())); //退案日 
			}
		}
		return workbook;
	}
	private String getNotBig(BigDecimal value) {
		if(value==null) {
			return "";
		}
		return value.toString();
	}
	private String getNotNull(String value) {
		if(value==null) {
			return "";
		}
		return value;
	}
	
}
